﻿SELECT 
 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
,H205.EVENT_ID
,H205.SERVICE_ORDER_NBR        		SONBR
,DISC_RSN_CD_U
,DISC_RSN_U     
,O.BAN
,H205.EVENT_POSTED_DT    			POSTDATE
,H205.EVENT_COMPLETION_DT    		CMPLDATE 
,H205.EVENT_CLASS_CD				ORDER_TYPE
,H205.DATA_SOURCE_CD				SOURCE_CD
,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
FROM
ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT 
H205
	,
	(
	SELECT 
	 O.ORDER_ID	
	,O.SERVICE_ORDER_CD
	,O.BAN
	,SALES_CHANNEL_CD
	,DISC_RSN_CD_U     
	,DISC_RSN_U
	FROM	  
	TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
	O					
	LEFT OUTER JOIN 
		(
		SELECT 
		 A.ORDER_ID
		,A.ORDER_ACTION_TYPE_CD
		,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
		,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
		FROM
		TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
		A 
		,
			TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
			C
			WHERE  A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
			AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
			AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
			AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
			AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
		)
		D
	ON O.ORDER_ID = D.ORDER_ID
	) 	
	O												
	WHERE  
	H205.EVENT_POSTED_DT = DATE-4 -----------------------
	AND H205.DATA_SOURCE_CD IN (150)
	AND O.ORDER_ID = SONBR
	AND RAAID > 0
	GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12  
